package net.lunjin.dao.impl;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import net.lunjin.bean.Remark;
import net.lunjin.dao.RemarkDao;
import finaler.util.Page;
import finaler.util.PageList;
import finaler.util.SQLUtil;

public class RemarkDaoImpl extends MiddleAbstractDao<Remark>  implements RemarkDao {

	@Override
	protected Class<Remark> getEntityClass() {
		return Remark.class;
	}
	
	@Override
	public PageList getRemarkList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		if(page.getParameter("orgId")!=null){
			whereSql.append(" where orgid = " + page.getParameter("orgId") );
		}
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from remark ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select *");
		queryListSql.append(" from remark  ");
		queryListSql.append( whereSql );
		queryListSql.append(" order by id desc");
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<Remark> dataList = super.findBySqlToBeanList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public Map<String, Object> getAvgFen(BigDecimal orgId) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append(" select z.*,round((avgycoin+avgdcoin+avgrcoin+avgkcoin)/4) as avgzcoin  from ");
		sql.append(" (");
		sql.append(" select round(avg(ycoin)) as avgycoin,");
		sql.append(" round(avg(dcoin)) as avgdcoin,");
		sql.append(" round(avg(rcoin)) as avgrcoin,");
		sql.append(" round(avg(kcoin)) as avgkcoin ");
		sql.append(" from remark where orgid= " + orgId);
		sql.append(" ) z "  );
		return super.findBySqlTohMap(sql.toString());
	}

	@Override
	public PageList getGoodOrBadList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" where " + page.getParameter("coinWhere"));
		//whereSql.append(" and autoflag = 0 ");
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from remark ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select rm.id,rm.rmcontent,org.city,orgid,org.orgname,rm.createtime ");
		queryListSql.append(" from remark rm ");
		queryListSql.append(" left join organizations org ");
		queryListSql.append(" on org.id = orgid ");
		queryListSql.append( whereSql );
		queryListSql.append(" order by id desc");
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public List<Map<String, Object>> getNew50Remark() throws Exception {
		String sql = " select r.id,r.rmcontent,r.orgid,o.orgname,o.city from remark r,organizations o  ";
				sql+=" where r.orgid = o.id order by r.id desc "  ;
				sql+=" limit 0,50 ";
		return super.findBySqlToMapList(sql);
	}

	@Override
	public List<Map<String, Object>> get5HighCoin() throws Exception {
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select b.hit,b.orgtel,b.city,b.id,b.orgname,round(avg(coin)) as avgcoin from remark,organizations b ");
		queryListSql.append(" where orgid = b.id ");
		queryListSql.append(" group by orgid order by avgcoin desc LIMIT 0,5 ");
		return super.findBySqlToMapList(queryListSql.toString());
	}

	@Override
	public List<Map<String, Object>> get5MostRemark() throws Exception {
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select b.hit,b.orgtel,b.city,b.id,b.orgname,count(1) as rmcount from remark,organizations b ");
		queryListSql.append(" where orgid = b.id ");
		queryListSql.append(" group by orgid order by rmcount desc LIMIT 0,5 ");
		return super.findBySqlToMapList(queryListSql.toString());
	}

	@Override
	public List<Map<String, Object>> getTopRemark(int number) throws Exception {
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select a.createuser,a.createtime,a.rmcontent,a.orgid,b.orgname from remark  a ");
		queryListSql.append(" left join organizations b on b.id = a.orgid ");
		queryListSql.append(" order by a.id desc limit "+number);
		return super.findBySqlToMapList(queryListSql.toString());
	}

	@Override
	public void deleteByOrgid(BigDecimal orgId) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from remark  where orgid="+orgId);
		super.executeSql(sql.toString());
	}
}
